﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Linq;
using Microsoft.Practices.EnterpriseLibrary.Data;


namespace JKF.Substructure
{
    /// <summary>
    /// 用事务招待一组sql语句
    /// </summary>
    public class ExecTransaction
    {
        public static void Exec(List<DbCommand> dbCommands)
        {
            Exec(DB.HR, dbCommands);
        }

        public static void Exec2(List<DbCommand> dbCommands)
        {
            var db = DB.HR;
            var i = 0;
            var id = Guid.NewGuid();
            foreach (var cmd in dbCommands)
            {
                db.ExecuteNonQuery(cmd);
                AppLogger.Log("调试", string.Format("{{{0}}}:[{1}]:{2}", id, i++, cmd.CommandText));
            }
        }


        public static void Exec(Database db, List<DbCommand> dbCommands)
        {
            if (dbCommands == null || dbCommands.Count == 0)
            {
                return;
            }
            using (DbConnection dbConnection = db.CreateConnection())
            {

                try
                {
                    dbConnection.Open();
                    using (DbTransaction dbTransaction = dbConnection.BeginTransaction(IsolationLevel.ReadCommitted))
                    {
                        var j = 0;
                        try
                        {
                            //dbCommands.Action(e => db.ExecuteNonQuery(e, dbTransaction));

                            foreach (DbCommand dbCommand in dbCommands)
                            {
                                j++;
                                db.ExecuteNonQuery(dbCommand, dbTransaction);
                            }
                            dbTransaction.Commit();

                        }
                        catch (Exception ex)
                        {
                            string sql = "";
                            foreach (DbCommand dbCommand in dbCommands.AsQueryable().Skip(j).Take(1))
                            {
                                sql += dbCommand.CommandText + "\r\n";
                                for (var i = 0; i < dbCommand.Parameters.Count; i++)
                                {
                                    var it = dbCommand.Parameters[i];
                                    if (it.DbType == DbType.Binary)
                                    {
                                        if (it.Value != null && it.Value != DBNull.Value)
                                            sql += " " + it.ParameterName + " : " + new Guid((byte[])it.Value) + "\r\n";
                                        else
                                        {
                                            sql += " " + it.ParameterName + " : " + it.Value + "\r\n";
                                        }
                                    }
                                    else
                                    {
                                        sql += " " + it.ParameterName + " : " + it.Value + "\r\n";
                                    }
                                }
                            }

                            AppLogger.Log("执行事务出错", sql);
                            dbTransaction.Rollback();
                            throw new Exception(ex.Message, ex);
                        }
                    }
                }
                catch (Exception ex)
                {
                    throw new Exception(ex.Message, ex);
                }
                finally
                {
                    try
                    {
                        dbConnection.Close();
                    }
                    catch (Exception ex)
                    {
                        throw new Exception("关闭连接失败", ex);
                    }

                }
            }
        }

        public static void Exec(List<string> sql)
        {
            List<string> sqltype = new List<string>();
            List<object> parameter = new List<object>();

            foreach (var s in sql)
            {
                sqltype.Add("sql");
                parameter.Add(null);
            }

            Exec(sqltype, sql, parameter);
        }

        public static void Exec(List<string> sqlType, List<string> sql, List<object> parameter)
        {
            Exec(sqlType.ToArray(), sql.ToArray(), parameter.ToArray());
        }

        /// <summary>
        /// 依先后顺序执行一组sql[三级参数维数相等]
        /// </summary>
        /// <param name="sqlType">执行语句类型,[sql/sp]</param>
        /// <param name="sql">sql语句或存储过程名称</param>
        /// <param name="parameter">存储过程名称</param>
        /// <returns></returns>
        public static void Exec(string[] sqlType, string[] sql, object[] parameter)
        {
            using (DbConnection dbConnection = DB.HR.CreateConnection())
            {

                try
                {
                    dbConnection.Open();
                    using (DbTransaction dbTransaction = dbConnection.BeginTransaction(IsolationLevel.ReadCommitted))
                    {
                        try
                        {
                            for (int i = 0; i < sql.Length; i++)
                            {
                                if (sqlType[i] == "sql")
                                {
                                    DB.HR.ExecuteNonQuery(dbTransaction,
                                                          CommandType.Text,
                                                          sql[i]);
                                }
                                else
                                {
                                    string sptemp = sql[i];
                                    object[] obj = (object[])parameter[i];
                                    DB.HR.ExecuteNonQuery(dbTransaction, sptemp, obj);
                                }

                            }

                            dbTransaction.Commit();
                        }
                        catch (Exception ex)
                        {
                            dbTransaction.Rollback();
                            throw new Exception("事务更新出错", ex);

                        }

                    }
                }
                catch (Exception ex)
                {
                    throw new Exception("打开连接失败", ex);
                }
                finally
                {
                    try
                    {
                        dbConnection.Close();
                    }
                    catch (Exception ex)
                    {
                        throw new Exception("关闭连接失败", ex);
                    }

                }


            }
        }
    }
}

